﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Globalization;

namespace Bussines
{
    #region DB_TABLES...
    public class clDB_TABLE
    {
        public string fDBT_TABLENAME;
        public string fDBT_DISPLAYNAME;
        public string fDBT_FILTER;
        public object fButton;

        public clDB_TABLE(string iDBT_TABLENAME, string iDBT_DISPLAYNAME, string iDBT_FILTER)
        {
            fDBT_TABLENAME = iDBT_TABLENAME;
            fDBT_DISPLAYNAME = iDBT_DISPLAYNAME;
            fDBT_FILTER = iDBT_FILTER;            
        }
    }

    public class clDB_TABLELista : List<clDB_TABLE>
    {
        private SqlConnection sc;

        public clDB_TABLELista(TBLObj iBLObj)
        {
            TBLObj pBLObj = iBLObj;
            sc = iBLObj.sqlConnection;

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = sc;

            cmd.CommandType = CommandType.Text;

            cmd.CommandText = "SELECT DBT_TABLENAME, DBT_DISPLAYNAME, DBT_FILTER FROM DB_TABLES " ;

            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                clDB_TABLE t = new clDB_TABLE(rdr["DBT_TABLENAME"].ToString() , rdr["DBT_DISPLAYNAME"].ToString(), rdr["DBT_FILTER"].ToString());
                this.Add(t);
            }
            rdr.Close();
        }

        public clDB_TABLE GetItemByText(string iText)
        {
            var L = from O in this
                    where O.fDBT_DISPLAYNAME == iText
                    select O;
            clDB_TABLE RetD = null;
            if (L.Count() > 0)
            {
                RetD = L.ElementAt(0);
            }
            return RetD;
        }

    }
    #endregion

    #region Stock...
    public class clStockData
    {
        public List<string> FieldNames = new List<string>();
        public List<object> FieldValues = new List<object>();
        public List<Type> FieldTypes = new List<Type>();
        public string TableName = "";
        public string IDFieldName = "";
        public string IDValue = "";        
    }

    public class clStock
    {
        private SqlConnection sc;
        private string pTableName;
        private clFIELDINFO_LIST pFieldInfo;
        private int pID;
        private string pFilter;
        public DataSet DS;
        SqlDataAdapter Adapter;
        TBLObj pBLObj = new TBLObj();

        public clStock(TBLObj iBLObj, string iTableName)
        {
            pBLObj = iBLObj;
            pTableName = iTableName;
            pFieldInfo = iBLObj.FieldInfo;
            sc = iBLObj.sqlConnection;            
        }

        private string Build_SELECT_SQL(List<clFIELDINFO> iFields)
        {
            string S = "SELECT ";
            foreach (clFIELDINFO Fi in iFields)
            {
                S = S + Fi.FieldName + ", ";
            }
            S=S.Remove(S.Length - 2, 2);
            S = S + " FROM " + pTableName;
            if (pID != -1)
            {
                S = S + " WHERE " + pFieldInfo.GetIDFieldName(pTableName) + "=" + pID.ToString();
                if (pFilter != "")
                {
                    S = S + " and " + pFilter;
                }
            }
            else
            {
                if (pFilter != "")
                {
                    S = S + " where " + pFilter;
                }
            }
            return S;
        }

        public DataSet GetData(int iID,string iFilter)
        {
            clFIELDINFO FI;
            pID = iID;
            pFilter = iFilter;
            DS = new DataSet();
            Adapter = new SqlDataAdapter();            

            Adapter.SelectCommand = new SqlCommand(Build_SELECT_SQL(pFieldInfo.GetFields(pTableName)), sc);
            Adapter.Fill(DS, pTableName);
            
            foreach (DataColumn DC in DS.Tables[0].Columns)
            { 
                FI=pFieldInfo.GetField(pTableName,DC.ColumnName);
                DC.Caption = FI.DisplayText;                
            }

            return DS;
        }

        public bool UpdateStock(clStockData iData, ref  string oErr)
        {
            SqlCommand cmd = new SqlCommand();

            oErr = "";

            string S = "";
            string Z = "";
            cmd.Connection = sc;

            S = "UPDATE " + iData.TableName + " SET ";
            
            int I=0;
            foreach (string F in iData.FieldNames)
            {
                S = S + F + "=";

                if (iData.FieldTypes[I] == typeof(string))
                {
                    try
                    {
                        S = S + "'" + iData.FieldValues[I].ToString() + "',";
                    }
                    catch 
                    {
                        S = S + "null,";
                    }
                }


                if (iData.FieldTypes[I] == typeof(DateTime))
                {
                    try
                    {
                        CultureInfo ci = CultureInfo.CurrentCulture;
                        DateTime D = (DateTime)iData.FieldValues[I];
                        //IFormatProvider iF=CultureInfo.CurrentCulture.
                        Z = D.ToString(ci.DateTimeFormat.ShortDatePattern);
                        if (Z.EndsWith("."))
                        {
                            Z = Z.Remove(Z.Length - 1, 1);
                        }
                        S = S + "'" + Z + "',";
                    }
                    catch
                    {
                        S = S + "null,";
                    }
                }                

                if (iData.FieldTypes[I] == typeof(byte) || iData.FieldTypes[I] == typeof(int) ||
                    iData.FieldTypes[I] == typeof(Int16) || iData.FieldTypes[I] == typeof(Int32) ||
                    iData.FieldTypes[I] == typeof(Int64) || iData.FieldTypes[I] == typeof(Decimal) ||
                    iData.FieldTypes[I] == typeof(Double) || iData.FieldTypes[I] == typeof(float)                     
                    )
                {
                    try
                    {
                        Z = iData.FieldValues[I].ToString();
                        Z = Z.Replace(',', '.');
                        if (Z == "")
                        {
                            S = S + "null,";
                        }
                        else
                        {
                            S = S + "" + Z + ",";
                        }
                    }
                    catch
                    {
                        S = S + "null,";
                    }
                }
                I++;
            }

            if (S.EndsWith(","))
            {
                S=S.Remove(S.Length-1, 1);
            }

            S = S + " WHERE " + iData.IDFieldName + "=" + iData.IDValue;
            
            cmd.CommandText = S;
            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {

                string s = "Hiba a törzsadat mentése közben!";
                Utilities.LogError(pBLObj, e, "clStock", s);
                oErr = s;
                return false;
            }

        }

        public bool DeleteStock(string iTableName, string iIDFieldName, int iID, ref string oErr)
        {
            SqlCommand cmd = new SqlCommand();            

            oErr = "";

            cmd.Connection = sc;

            cmd.CommandText = "DELETE FROM " + iTableName +
                " WHERE " + iIDFieldName + "=" + iID.ToString();

            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {

                string s = "Hiba a törzsadat törlése közben!";
                Utilities.LogError(pBLObj, e, "clStock", s);
                oErr = s;
                return false;
            }
        }

        public bool InsertStock(clStockData iData, ref  string oErr)
        {
            SqlCommand cmd = new SqlCommand();

            oErr = "";

            int I = 0;

            string Z = "";

            string S = "INSERT INTO " + iData.TableName+"(";

            foreach (string F in iData.FieldNames)
            {
                S = S + F + ",";                
            }

            if (S.EndsWith(","))
            {
                S = S.Remove(S.Length - 1, 1);
            }

            S = S + ") VALUES (";

            //S = "";

            foreach (string F in iData.FieldNames)
            {
                if (iData.FieldTypes[I] == typeof(string))
                {
                    try
                    {
                        S = S + "'" + iData.FieldValues[I].ToString() + "',";
                    }
                    catch 
                    {
                        S = S + "null,";
                    }
                }

                if (iData.FieldTypes[I] == typeof(DateTime))
                {
                    try
                    {
                        CultureInfo ci = CultureInfo.CurrentCulture;
                        DateTime D =(DateTime)iData.FieldValues[I];
                        //IFormatProvider iF=CultureInfo.CurrentCulture.
                        Z = D.ToString(ci.DateTimeFormat.ShortDatePattern);
                        if (Z.EndsWith("."))
                        {
                            Z = Z.Remove(Z.Length - 1, 1);
                        }
                        S = S + "'" + Z + "',";
                    }
                    catch
                    {                       
                        S = S + "null,";
                    }
                }

                if (iData.FieldTypes[I] == typeof(byte) || iData.FieldTypes[I] == typeof(int) ||
                    iData.FieldTypes[I] == typeof(Int16) || iData.FieldTypes[I] == typeof(Int32) ||
                    iData.FieldTypes[I] == typeof(Int64) || iData.FieldTypes[I] == typeof(Decimal) ||
                    iData.FieldTypes[I] == typeof(Double) || iData.FieldTypes[I] == typeof(float)
                    )
                {
                    try
                    {
                        Z = iData.FieldValues[I].ToString();
                        Z = Z.Replace(',', '.');
                        if (Z == "")
                        {
                            S = S + "null,";
                        }
                        else
                        {
                            S = S + "" + Z + ",";
                        }
                    }
                    catch 
                    {
                        S = S + "null,";
                    }
                }
                I++;
            }

            if (S.EndsWith(","))
            {
                S = S.Remove(S.Length - 1, 1);
            }

            S = S + ")";

            cmd.Connection = sc;
            cmd.CommandText = S;
            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {

                string s = "Hiba a törzsadat mentése közben!";
                Utilities.LogError(pBLObj, e, "clStock", s);
                oErr = s;
                return false;
                //Console.WriteLine("err : " + e.Message.ToString());
            }           
        }

        //private void Init(TBLObj iBLObj, string iTableName, int iID)
        //{
        //    TBLObj pBLObj = iBLObj;
        //    sc = iBLObj.sqlConnection;
        //    pFieldInfo = pBLObj.FieldInfo;
        //    pTableName = iTableName;

        //    SqlDataAdapter A = new SqlDataAdapter(Build_SELECT_SQL(pFieldInfo.GetFields(pTableName)), sc);
        //    DS = new DataSet();

        //    DS = new SqlDataSource();
        //    DS.

        //    A.Fill(DS);
        //}
    }
    #endregion
}
